package com.xliang.spider.util;

import com.xliang.db.DBUtil;
import com.xliang.spider.version03.pojo.CourseEntity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by xliang on 2016/6/9.
 */
public class CourseUtil
{
    
    public static boolean insertCourse(CourseEntity course)
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            conn = DBUtil.open();
            String sql = "insert into course(course_id, course_name, chapter_name, part_name, url) values(?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setInt(1, course.getCourseId());
            pstmt.setString(2, course.getCourseName());
            pstmt.setString(3, course.getChapterName());
            pstmt.setString(4, course.getPartName());
            pstmt.setString(5, course.getUrl());
            pstmt.executeUpdate();
            success = true;
            System.out.println("插入视频："+course.getChapterName()+"_"+course.getPartName());
        }
        catch (Exception e)
        {
            if (e.getMessage().contains("Duplicate") && e.getMessage().contains("uniq_course_name_and_chapter_name_and_part_name"))
            {
                updateCourse(course);
                success = true;
            }else
            {
                e.printStackTrace();
            }
        }
        finally
        {
            DBUtil.close(pstmt);
            DBUtil.close(conn);
        }
        return success;
    }
    
    public static void updateCourse(CourseEntity course)
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try
        {
            conn = DBUtil.open();
            String sql = "update course set url=?, status=? where course_id=? and course_name=? and chapter_name=? and part_name=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, course.getUrl());
            pstmt.setInt(2, course.getStatus());
            pstmt.setInt(3, course.getCourseId());
            pstmt.setString(4, course.getCourseName());
            pstmt.setString(5, course.getChapterName());
            pstmt.setString(6, course.getPartName());
            pstmt.executeUpdate();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            DBUtil.close(pstmt);
            DBUtil.close(conn);
        }
        System.out.println("更新视频："+course.getChapterName()+"_"+course.getPartName());
    }
    
    public static List<CourseEntity> findCourseList(int status)
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<CourseEntity> coursies = new ArrayList<>();
        try
        {
            conn = DBUtil.open();
            String sql = "select * from course where status = ? ORDER BY id limit 10";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, status);
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                CourseEntity se = new CourseEntity();
                se.setId(rs.getInt("id"));
                se.setCourseId(rs.getInt("course_id"));
                se.setCourseName(rs.getString("course_name"));
                se.setChapterName(rs.getString("chapter_name"));
                se.setPartName(rs.getString("part_name"));
                se.setUrl(rs.getString("url"));
                coursies.add(se);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            DBUtil.close(rs);
            DBUtil.close(pstmt);
            DBUtil.close(conn);
        }
        return coursies;
    }
}
